#region Imported Namespaces

using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using PHSRAG.Insight21.InsightUtilities;
using System.Text;

using BO				   = PHSRAG.Insight21.BusinessObjects;
using DataAccess		= PHSRAG.Utility.DataAccess;
using SearchCriteria	= PHSRAG.Insight21.SearchContext.SearchCriteria;
using PHSRAG.Insight21.SearchContext;

#endregion

namespace PHSRAG.Insight21.Purchasing
{
	/// <summary>
	/// POAPSummaries Class retrieves the POAP summary resultset and creates a
	/// DataView used to sort, filter and calculate Total$ amount for OrderAmount, ExpensedToDate
	/// EncumAmount, InvoiceAmount and  the data.  It returns a resultset made up of
	/// 2 tables.  The first contains the POAP Summaries  and 2nd contains the totals.
	/// Derives from Transactions class.
	/// </summary>
	public class POAPSummaries : Transactions 
	{
		#region Instance Variables
		/// <summary>
		/// Private Instance variables used for sorting dataview.
		/// </summary>
		private Hashtable		poapSummaryMap			= new Hashtable();
		private string			currentDataTableName	= Constants.OriginalDataTableName;
		private bool			sortAscending			= false;
		private string			sortColumn				= "InvoiceDate";	// Keep this in sync with what the stored proc orders by.
		
		// Set default to and from dates, for puchasing data is by default retreived for last 60 days
		private string defaultFromDate		=	DateTime.Now.AddDays
			(Utility.Settings.GetKeyValue("Purchasing.DefaultFromDate", -60)).ToShortDateString();
		private string defaultToDate		=	DateTime.Now.ToShortDateString();

		#endregion

		#region Constructor
		/// <summary>
		/// Constructor, and calls the base class constructor for Cache Policy
		/// </summary>
		public POAPSummaries() : base(null)
		{
		}
		#endregion

		#region Baseclass Transactions
				
		#region Properties
		/// <summary>
		/// Get the key to be used in all caching operations for this instance of the object.
		/// </summary>
		public static string CacheKey	{ get { return Constants.POAPSummariesKey; } }

		/// <summary>
		/// Get the total number of POAP Transactions.
		/// </summary>
		public override int Count
		{
			get {  return (DataContainer.Tables.Count == 0) ? 0 : DataContainer.Tables[Constants.OriginalDataTableName].Rows.Count; }
		}
		  
		/// <summary>
		/// Get the sort sequence.
		/// </summary>
		public override bool SortAscending
		{
			get { return sortAscending; }
			set { sortAscending = value; }
		}

		/// <summary>
		/// Get the name of the sort column.
		/// </summary>
		public override string SortColumn
		{
			get { return sortColumn; }
			set { sortColumn = value; }
		}	
		
		#endregion

		#region Methods

		/// <summary>
		/// Sort the list of poap summaries by the speicified column.
		/// If the column is to be sorted is the same as what was last sorted on, 
		/// the sort sequence is toggled. Else, the
		/// sort sequence is set to ascending. the routine extracts 
		/// the tables(POAP information table and Totals Table) from the DataContainer and 
		/// POAP Summary is sorted using Dataview sort method.
		/// Orginal table is removed and sorted table is included back
		/// </summary>
		/// <param name="columnName">Column to sort by</param>
		public override void Sort(string columnName)
		{

			poapSummaryMap.Clear();

			//sortAscending = (sortColumn == columnName) ? !sortAscending : true;
			sortColumn = columnName;

			DataTable dataTable = DataContainer.Tables[currentDataTableName];

			DataView dataView = dataTable.DefaultView;
			dataView.Sort = string.Format("{0} {1}", columnName, sortAscending ? "ASC" : "DESC");

			DataTable sortedDataTable = dataTable.Clone();
			foreach (DataRowView rowView in dataView)
				sortedDataTable.ImportRow(rowView.Row);

			DataContainer.Tables.Remove(currentDataTableName);
			DataContainer.Tables.Add(sortedDataTable);
		
		}
		
		
		/// <summary>
		/// Get the list of poap objects bound by a start and end index. These indeces are into the list of poap
		/// summaries that was loaded during the Load() method execution into the data container. Typically, the start index
		/// is on a "page" boundary, where the "page" referes to a datagrid page.
		/// Note that the total number of poap objects returned may be less than the requested count.
		/// We maintain a Hashtable of ArrayList objects using the start index as the key. This allows us to retrieve the
		/// set of POAP objects associated with that index, thereby directly supporting random access based the
		/// page index.
		/// If such an ArrayList does not exist for the current page index, we load them using the list of poap IDs.
		/// The start end end indeces into that list are computed based on the current page index and the datagrid page size.
		/// POAP objects are loaded based on the summaries which falls within the compute start and end indeces, 
		/// and saved in an ArrayList. That ArrayList is then stored in the Hashtable with the current page index as its
		/// key.
		/// </summary>
		/// <param name="startIndex">Index within the poap summary list to begin at</param>
		/// <param name="count">Total number of poap objects to load</param>
		/// <returns>The list of poap objects</returns>
		public override ArrayList GetPurchasingTransactionList(int startIndex, int count)
		{
			ArrayList poapList = poapSummaryMap[startIndex] as ArrayList;
			if (poapList == null)
			{
				poapList = new ArrayList();
				DataTable dataTable = DataContainer.Tables[currentDataTableName];
				for (int i = startIndex, end = Math.Min(i + count, dataTable.Rows.Count); i < end; ++i)
					poapList.Add(new POAPSummary(CachePolicy, dataTable.Rows[i]));
				poapSummaryMap[startIndex] = poapList;
			}
			return poapList;
		}

		#endregion
		
		#endregion

		#region BusinessObject Methods
		/// <summary>
		/// Load the current instances with summary data for the current user based on Search Criterias
		/// the load method adds Totals table to the DataContainer, this totals table stores
		/// the grand total of Order Amount, ExpensedToDate,Encumbrance Amount and InvoiceAmount.
		/// the dataview and datatables compute and sum methods are used to calculate the totals.
		/// </summary>
		/// <param name="connectionString">Database connection string</param>
		/// <param name="args">consists of search filter paramaters for POAP Summaries</param>
		public override void Load(string connectionString, params object[] args)
		{
			if (args.Length < 5)
				throw new Exception("POAP Summaries load requires 5 parameters.");

			if (args[0] == null )
				throw new Exception("POAP Summaries load requires a session id");

			if (args[1] == null )
				throw new Exception("POAP Summaries load requires Search Criteria containing at least a domain user ID");

			string moduleContext = args[4] as string;
			if(moduleContext == null || moduleContext == string.Empty)
				throw new Exception("POAP Summaries load requires module context parameter with value of either Agreements or Purchasing.");

			SearchCriteria commonSearchCriteria = (SearchCriteria) args[1];

			string sessionId = (string) args[0];
			if(commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.RequestorNameKey] != null)
				commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.RequestorNameKey] = commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.RequestorNameKey].Replace("'", "''");
			if(commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.VendorNameKey] != null)
				commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.VendorNameKey] = commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.VendorNameKey].Replace("'", "''");
			
			if(DataContainer != null)
				DataContainer.Reset();
			if (moduleContext == InsightUtilities.Constants.Modules.Agreements) 
			{
				DataContainer  = DataAccess.GetDataSet(connectionString, 
					"GetAgreementTransactionSummary" ,
					DataAccess.BuildSqlParameterArray(
					"@sessionID", 				SqlDbType.VarChar, 		sessionId,
					"@domainUserID", 			SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.DomainUserIDKey],
					"@poNumber", 				SqlDbType.VarChar, 		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.PONumberKey],
					"@voucherNumber", 			SqlDbType.VarChar, 		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.VoucherNumberKey],
					"@orderTypeId", 			SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.OrderTypeKey],
					"@vendorId", 				SqlDbType.Int,			null,//can be removed
					"@vendorName",				SqlDbType.VarChar, 		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.VendorNameKey],
					"@fundId" ,					SqlDbType.Int,			null,//can be removed
					"@fundNumber", 				SqlDbType.VarChar, 		(args[2] == null ? commonSearchCriteria[InsightUtilities.Constants.FundIDKey] : args[2].ToString() ),//commonSearchCriteria[InsightUtilities.Constants.FundIDKey],
					"@requestorName", 			SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.RequestorNameKey],
					"@piId",  					SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.PIIDKey],
					"@labId", 					SqlDbType.Int, 			null,//can be removed
					"@accountNumber", 			SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.AccountNumberKey],
					"@accountCategoryId", 		SqlDbType.Int, 			commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.CategoryIdKey],
					"@requisitionNumber", 		SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.RequisitionNumberKey],
					"@expenseStatus",  			SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.ExpenseStatusViewKey],//expenseStatus,
					"@invoiceNumber",  			SqlDbType.VarChar, 		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.InvoiceNumberKey],
					"@journalNumber",			SqlDbType.VarChar, 		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.JournalNumberKey],//args[(int)Enumerations.SearchElements.JournalNumber],					
					"@transactionType",			SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.TransactionTypeKey],
					"@transactionTypeFrom" ,	SqlDbType.DateTime,		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.TransactionDateFromKey],
					"@transactionTypeTo",		SqlDbType.DateTime, 	commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.TransactionDateToKey],
					"@enteredBy", 				SqlDbType.VarChar, 		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.EnteredByKey],
					"@locationId",				SqlDbType.Int, 			commonSearchCriteria[InsightUtilities.Constants.LocationIDKey],
					"@chiefCodeList",			SqlDbType.VarChar, 		commonSearchCriteria.COSIds,
					"@includeProgramProjects",	SqlDbType.Bit,			commonSearchCriteria[InsightUtilities.Constants.IncludeProgramProjectsKey],
					"@folderNumber",			SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.FolderNumberKey],
					"@bicsNumber",				SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.BicsNumberKey],
					"@walkerNumber",			SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.WalkerNumberKey],
					"@sponsorNumber",			SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.SponsorNumberKey],
					"@sponsorName",				SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.SponsorNameKey],
					"@sponsorTypeId",			SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.SponsorTypeIDKey],
					"@agreementTypeId",			SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.AgreementTypeIDKey],
					"@activityCode",			SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.ActivityCodeKey],
					"@budgetStartDate",			SqlDbType.DateTime,		commonSearchCriteria[InsightUtilities.Constants.BudgetStartDateKey],
					"@budgetEndDate",			SqlDbType.DateTime,		commonSearchCriteria[InsightUtilities.Constants.BudgetEndDateKey],
					"@projectStartDate",		SqlDbType.DateTime,		commonSearchCriteria[InsightUtilities.Constants.ProjectStartDateKey],
					"@projectEndDate",			SqlDbType.DateTime,		commonSearchCriteria[InsightUtilities.Constants.ProjectEndDateKey],
					"@sponsorGroupTypeId",		SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.ViewingOptionKey],
					"@budgetEndingDays",		SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.DaysBudgetEndingKey],
					"@projectEndingDays",		SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.DaysProjectEndingKey],
					"@daysFundCreated",			SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.DaysFundCreatedKey],
					"@lowBalance",				SqlDbType.Decimal,		commonSearchCriteria[InsightUtilities.Constants.BalanceLessThanKey],
					"@highBalance",				SqlDbType.Decimal,		commonSearchCriteria[InsightUtilities.Constants.BalanceGreaterThanKey],
					"@agreementStatusTypeList",	SqlDbType.VarChar,		((bool)args[6])? " " : commonSearchCriteria[InsightUtilities.Constants.AgreementStatusIDKey],
					"@fundStatusTypeId",		SqlDbType.Int,			((bool)args[5]) ? "0" : commonSearchCriteria[InsightUtilities.Constants.FundStatusIDKey],
					"@accessBitMask",			SqlDbType.Int,			args[3] == null ? 0 : args[3],
                    "@detail", SqlDbType.Bit, commonSearchCriteria[InsightUtilities.Constants.DetailsKey] != null ? Convert.ToBoolean(commonSearchCriteria[InsightUtilities.Constants.DetailsKey]) : false,
                    "@Cumulative", SqlDbType.Int, commonSearchCriteria[InsightUtilities.Constants.CumulativeKey]
                    ));
			}
			else
			{
				DataContainer  = DataAccess.GetDataSet(connectionString, 
					"GetPOAPSummary",
					DataAccess.BuildSqlParameterArray(
					"@domainUserID", 			SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.DomainUserIDKey],
					"@poNumber", 				SqlDbType.VarChar, 		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.PONumberKey],
					"@voucherNumber", 			SqlDbType.VarChar, 		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.VoucherNumberKey],
					"@orderTypeId", 			SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.OrderTypeKey],
					"@vendorId", 				SqlDbType.Int,			null,//can be removed
					"@vendorName",				SqlDbType.VarChar, 		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.VendorNameKey],
					"@fundId" ,					SqlDbType.Int,			null,//can be removed
					"@fundNumber", 				SqlDbType.VarChar, 		(args[2] == null ? commonSearchCriteria[InsightUtilities.Constants.FundIDKey] : args[2].ToString() ),//commonSearchCriteria[InsightUtilities.Constants.FundIDKey],
					"@requestorName", 			SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.RequestorNameKey],
					"@piId",  					SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.PIIDKey],
					"@labId", 					SqlDbType.Int, 			null,//can be removed
					"@accountNumber", 			SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.AccountNumberKey],
					"@accountCategoryId", 		SqlDbType.Int, 			commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.CategoryIdKey],
					"@requisitionNumber", 		SqlDbType.VarChar,		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.RequisitionNumberKey],
					"@expenseStatus",  			SqlDbType.Int,			commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.ExpenseStatusViewKey],//expenseStatus,
					"@invoiceNumber",  			SqlDbType.VarChar, 		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.InvoiceNumberKey],
					//Journal Number in case of POAP will always zero, and thus is not required to be used for filter. 
					//"@journalNumber",			SqlDbType.VarChar, 		args[(int)Enumerations.SearchElements.JournalNumber],					
					"@transactionType",			SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.TransactionTypeKey],
					"@transactionTypeFrom" ,	SqlDbType.DateTime,		(commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.TransactionDateFromKey] == null ?  defaultFromDate : commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.TransactionDateFromKey]),
					"@transactionTypeTo",		SqlDbType.DateTime, 	(commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.TransactionDateToKey] == null ? defaultToDate : commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.TransactionDateToKey]),
					"@enteredBy", 				SqlDbType.VarChar, 		commonSearchCriteria[InsightUtilities.Constants.PurchasingSearchKey.EnteredByKey],
					"@locationId",				SqlDbType.Int, 			commonSearchCriteria[InsightUtilities.Constants.LocationIDKey],
					"@chiefCodeList",			SqlDbType.VarChar, 		commonSearchCriteria.COSIds,
					"@includeProgramProjects",	SqlDbType.Bit,		commonSearchCriteria[InsightUtilities.Constants.IncludeProgramProjectsKey],
					"@folderNumber",			SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.FolderNumberKey],
					"@bicsNumber",				SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.BicsNumberKey],
					"@walkerNumber",			SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.WalkerNumberKey],
					"@sponsorNumber",			SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.SponsorNumberKey],
					"@sponsorName",				SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.SponsorNameKey],
					"@sponsorTypeId",			SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.SponsorTypeIDKey],
					"@agreementTypeId",			SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.AgreementTypeIDKey],
					"@activityCode",			SqlDbType.VarChar,	commonSearchCriteria[InsightUtilities.Constants.ActivityCodeKey],
					"@budgetStartDate",			SqlDbType.DateTime, commonSearchCriteria[InsightUtilities.Constants.BudgetStartDateKey],
					"@budgetEndDate",			SqlDbType.DateTime, commonSearchCriteria[InsightUtilities.Constants.BudgetEndDateKey],
					"@projectStartDate",		SqlDbType.DateTime, commonSearchCriteria[InsightUtilities.Constants.ProjectStartDateKey],
					"@projectEndDate",			SqlDbType.DateTime, commonSearchCriteria[InsightUtilities.Constants.ProjectEndDateKey],
					"@sponsorGroupTypeId",		SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.ViewingOptionKey],
					"@budgetEndingDays",		SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.DaysBudgetEndingKey],
					"@projectEndingDays",		SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.DaysProjectEndingKey],
					"@daysFundCreated",			SqlDbType.Int,		commonSearchCriteria[InsightUtilities.Constants.DaysFundCreatedKey],
					"@lowBalance",				SqlDbType.Decimal,	commonSearchCriteria[InsightUtilities.Constants.BalanceLessThanKey],
					"@highBalance",				SqlDbType.Decimal,	commonSearchCriteria[InsightUtilities.Constants.BalanceGreaterThanKey],
					"@agreementStatusTypeList",	SqlDbType.VarChar,	((bool)args[6])? " " : commonSearchCriteria[InsightUtilities.Constants.AgreementStatusIDKey],
					"@FundStatusTypeId",		SqlDbType.Int,		((bool)args[5]) ? "0" : commonSearchCriteria[InsightUtilities.Constants.FundStatusIDKey],
					"@accessBitMask",			SqlDbType.Int,		args[3] == null ? 0 : args[3]
					));
			}

			if(DataContainer.Tables.Count !=1)
				throw new Exception("Error in Executing POAP Summaries StoreProc");

			DataContainer.Tables[0].TableName = Constants.OriginalDataTableName;

			DataTable totalsDataTable	= new DataTable(Constants.TotalsTableName);
			
			totalsDataTable.Columns.Add(new DataColumn(Constants.TotalOrderAmount, typeof(Decimal)));
			totalsDataTable.Columns.Add(new DataColumn(Constants.TotalExpensedToDate, typeof(Decimal)));
			totalsDataTable.Columns.Add(new DataColumn(Constants.TotalEncumbranceAmount, typeof(Decimal)));
			totalsDataTable.Columns.Add(new DataColumn(Constants.TotalInvoiceAmount, typeof(Decimal)));

			DataTable POAPsTable = DataContainer.Tables[Constants.OriginalDataTableName];
			
			if (POAPsTable.Rows.Count > 0)
			{
				DataRow totalsRow = totalsDataTable.NewRow();				
				
				object o = (POAPsTable.Compute("sum(OrderAmount)", string.Empty));
				totalsRow[Constants.TotalOrderAmount]			= (o == System.DBNull.Value)? 0 : Convert.ToDecimal(o);

				o = (POAPsTable.Compute("sum(ExpensedToDate)", string.Empty));
				totalsRow[Constants.TotalExpensedToDate]		= (o == System.DBNull.Value)? 0 : Convert.ToDecimal(o);

				o = (POAPsTable.Compute("sum(EncumbranceAmount)", string.Empty));
				totalsRow[Constants.TotalEncumbranceAmount]		= (o == System.DBNull.Value)? 0 : Convert.ToDecimal(o);

				o = (POAPsTable.Compute("sum(InvoiceAmount)", string.Empty));
				totalsRow[Constants.TotalInvoiceAmount]			= (o == System.DBNull.Value)? 0 : Convert.ToDecimal(o);
				
				totalsDataTable.Rows.Add(totalsRow);
			}
			DataContainer.Tables.Add(totalsDataTable);

		}
		#endregion
	}
}
// --- EOF ---